﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using ZX.Aspose;
using ZX.DBModule;
using ZX.BLL.Extend;

namespace ZX.DataImport
{
    public class DataImportBase
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public System.Data.DataSet GetExeclSet(string FileName)
        {
            ImportCell importCell = new ImportCell(FileName);
            return importCell.GetSheetDataToDataSet();
        }

        /// <summary>
        /// 获得EXECL集合
        /// </summary>
        /// <param name="FileName"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public System.Data.DataTable GetExeclTable(string FileName, string TableName)
        {
            return GetExeclSet(FileName).Tables[TableName];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public System.Data.DataTable GetExeclTable(string FileName)
        {
            return GetExeclSet(FileName).Tables[0];
        }

        /// <summary>
        /// 避免重复导入数据
        /// </summary>
        /// <param name="sourceDT"></param>
        /// <param name="TableName"></param>
        /// <param name="strWhere"></param>
        /// <param name="Fields"></param>
        public void Import(DataTable sourceDT, string TableName, string strWhere,string Fields)
        {
            ZX.BLL.Extend.BaseBussnies bll = new BaseBussnies();
            System.Data.DataTable dt = bll.GetDataBySqlComm("Select * from SysImportSetting where tableName='" + TableName + "'");
            dt.Select().ToList();
            (new ZX.DataImport.DataConvert()).UpdateDataTableColumnName(sourceDT, dt);
            foreach (DataRow row in sourceDT.Rows)
            {
                string[] _f = Fields.Split(',');
                object[] _obj = new object[_f.Length];
                for(int _index =0;_index<_f.Length;_index++)
                {
                    _obj[_index] = row[_f[_index]].ToString();
                }
                string strSql  = "select * from "+TableName+ " where "+ string.Format(strWhere, _obj);
                System.Data.DataTable _dt = bll.GetDataBySqlComm(strSql);
                if (_dt.Rows.Count>0)
                {
                    continue;
                }

                DataTableBaseEntity entity = new DataTableBaseEntity();
                entity.TableName = TableName;
                string[] strKeyArrary = entity.GetKey();
                if (strKeyArrary != null)
                {
                    entity.TableKey = strKeyArrary[0];
                    entity.KeyType = strKeyArrary[1];
                }
                entity.CreateDT();
                foreach (System.Data.DataColumn col in sourceDT.Columns)
                {
                    try
                    {
                        entity.SetValue(col.ColumnName, row.ItemArray[sourceDT.Columns[col.ColumnName].Ordinal]);
                        if (col.ColumnName == "EmployeeCode")
                        {
                            DataRow dr = GetEmployee(row.ItemArray[sourceDT.Columns["EmployeeCode"].Ordinal].ToString());
                            entity.SetValue("EmployeeID", dr.ItemArray[dr.Table.Columns["ID"].Ordinal]);
                        }
                    }
                    catch
                    {

                    }
                }
                bll.entity = entity;
                bll.Insert();
            }
        }

        public void Import(DataTable sourceDT, string TableName)
        {
            ZX.BLL.Extend.BaseBussnies bll = new BaseBussnies();
            System.Data.DataTable dt = bll.GetDataBySqlComm("Select * from SysImportSetting where tableName='" + TableName + "'");
            dt.Select().ToList();
            (new ZX.DataImport.DataConvert()).UpdateDataTableColumnName(sourceDT, dt);
            foreach (DataRow row in sourceDT.Rows)
            {
                DataTableBaseEntity entity = new DataTableBaseEntity();
                entity.TableName = TableName;
                string[] strKeyArrary = entity.GetKey();
                if (strKeyArrary != null)
                {
                    entity.TableKey = strKeyArrary[0];
                    entity.KeyType = strKeyArrary[1];
                }
                entity.CreateDT();
                foreach (System.Data.DataColumn col in sourceDT.Columns)
                {
                    try
                    {
                        entity.SetValue(col.ColumnName, row.ItemArray[sourceDT.Columns[col.ColumnName].Ordinal]);
                        if (col.ColumnName == "EmployeeCode")
                        {
                            DataRow dr =GetEmployee(row.ItemArray[sourceDT.Columns["EmployeeCode"].Ordinal].ToString());
                            entity.SetValue("EmployeeID", dr.ItemArray[dr.Table.Columns["ID"].Ordinal]);
                        }
                    }
                    catch
                    {
                        
                    }
                }
                bll.entity = entity;
                bll.Insert();
            }
        }

        /// <summary>
        /// 获取人事信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetEmployees()
        {
             BaseBussnies bll = new BaseBussnies();
             return bll.GetDataBySqlComm("select * from V_hrEmployee");
        }



        /// <summary>
        /// 根据员工编码获取人事信息
        /// </summary>
        /// <param name="EmployeeCode">员工编码</param>
        /// <returns></returns>
        public DataRow GetEmployee(string EmployeeCode)
        {
            if (EmployeesData == null)
            {
                EmployeesData = GetEmployees();
            }
            DataRow[] drs = EmployeesData.Select("EmployeeCode='" + EmployeeCode + "'");
           if (drs.Count() > 0)
           {
               return drs[0];
           }
           return null; 
        }

        /// <summary>
        /// 
        /// </summary>
        private DataTable _EmployeesData=null;
        public DataTable EmployeesData
        {
            get
            {
                return _EmployeesData;
            }
            set
            {
                _EmployeesData = value;
            }
        }

        /// <summary>
        /// 数据导入
        /// </summary>
        /// <param name="FileName">文件</param>
        /// <param name="TableName">表名</param>
        public void Import(string FileName, string TableName)
        {
            //iSun.Bussiness.Base.BaseBussnies bll = new BaseBussnies();
            //System.Data.DataTable dt = bll.GetDataBySqlComm("Select * from SysImportSetting where tableName='"+TableName+"'");
            
            DataTable dt2 = GetExeclTable(FileName);
            Import(dt2, TableName);
        }
    }
}
